/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package it.barattando.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author carlo.bison
 */
public class DbController {
    
    private Connection connection;
    
    public DbController(String confFileName) throws NamingException, SQLException{
        InitialContext context=new InitialContext();
        DataSource ds=(DataSource)context.lookup("java:comp/env/"+confFileName);
        this.connection = ds.getConnection();
    }
    
    public LinkedList<Oggetto> getOggetti(String userName) throws SQLException{
        
        LinkedList<Oggetto> result=new LinkedList<Oggetto>();
        
        PreparedStatement stm = this.connection.prepareStatement("SELECT OGGETTO,OGGETTO_DESCR, OGGETTO_CATEGORA, OGGETTO_CREDITI_VAL FROM TBAN_OGGETTI WHERE USER_ID = ?");
        stm.setString(1,userName);
        
        ResultSet rs = stm.executeQuery();
        
        Oggetto newOggetto;
        
        while (rs.next()){
            newOggetto = new Oggetto(rs.getString("OGGETTO"),rs.getString("OGGETTO_DESCR"),rs.getString("OGGETTO_CATEGORA"),rs.getString("OGGETTO_CREDITI_VAL"),userName);
            result.add(newOggetto);
        }
        
        return result;
        
    }
    
    public boolean verificaCredenziali(String userName,String pwd) throws SQLException{
        try{
            PreparedStatement stm=this.connection.prepareStatement("SELECT * FROM TBAN_UTENTI WHERE USER_ID=? AND USER_PWD=?");
            stm.setString(1,userName);
            stm.setString(2, pwd);
            System.out.println("esecuzione prepared statement");
            ResultSet rs=stm.executeQuery();
            System.out.println("res: "+rs.toString());
            if (rs.next())
                return true;
            else
                return false;
    
        }catch(SQLException sqle){
            throw sqle;
        }
    }
    
    public boolean inserisciNuovoOggetto(Oggetto nuovoOggetto,String userName)throws SQLException{
        
        try{
            System.out.println("creazione prapared statement "+userName+" "+nuovoOggetto.getDescrizione()+" "+nuovoOggetto.getNome()+" "+nuovoOggetto.getCategoria()+" "+nuovoOggetto.getValore());
            PreparedStatement stm=this.connection.prepareStatement("INSERT INTO TBAN_OGGETTI (OGGETTO,OGGETTO_DESCR,OGGETTO_CATEGORA,USER_ID,OGGETTO_CREDITI_VAL) VALUES (?,?,?,?,?)");
            stm.setString(1,nuovoOggetto.getNome());
            stm.setString(2,nuovoOggetto.getDescrizione() );
            stm.setString(3,nuovoOggetto.getCategoria());
            stm.setString(4,userName );
            stm.setString(5,nuovoOggetto.getValore() );
            System.out.println("esecuzione query");
            int rs=stm.executeUpdate();
            System.out.println("query eseguita");
            if(rs==1)
                return true;
            else
                return false;
    
        }catch(SQLException sqle){
            System.out.println(sqle.getLocalizedMessage());
            throw sqle;
        }
        
    }
        
        public boolean aggiungiUtente(String userName,String pwd,String nome,String cognome,String email)throws SQLException{
        try{
            PreparedStatement stm=this.connection.prepareStatement("INSERT INTO TBAN_UTENTI (USER_ID,USER_PWD,USER_NAME,USER_SURNAME,USER_EMAIL) VALUES (?,?,?,?,?)");
            stm.setString(1,userName);
            stm.setString(2, pwd);
            stm.setString(3,nome);
            stm.setString(4, cognome);
            stm.setString(5, email);
            int rs=stm.executeUpdate();
            if(rs==1)
                return true;
            else
                return false;
    
        }catch(SQLException sqle){
            throw sqle;
        }
        }
        
        
    
    
}